package com.viettel.core.dao.cms;

import java.util.ArrayList;
import java.util.List;

import org.hibernate.type.StandardBasicTypes;
import org.hibernate.type.Type;
import org.springframework.beans.factory.annotation.Autowired;

import com.viettel.core.common.utils.StringUtil;
import com.viettel.core.entities.cms.ArticleCategory;
import com.viettel.core.entities.vo.ArticleCategorySearchFilter;
import com.viettel.core.entities.vo.ArticleCategorySearchVO;
import com.viettel.core.entities.vo.ReportSubSearchFilter;
import com.viettel.core.entities.vo.ReportSubSearchVO;
import com.viettel.core.enumtype.ArticleCategoryStatus;
import com.viettel.core.exceptions.DataAccessException;
import com.viettel.core.repo.IRepository;

/**
 *  @author: HUYNHDC
 *  @version:  1.0
 *  @since: 16/07/2014 - 10:50:59
 */
public class ReportSubDAOImpl implements ReportSubDAO{
private IRepository repo; 
	
 	public void setRepo(IRepository repo) {
		this.repo = repo;
	} 

	@Override
	public List<ReportSubSearchVO> getListReportSubByFilter(ReportSubSearchFilter filter)
			throws DataAccessException {// TODO
		List<Object> paramSelect = new ArrayList<Object>();
		List<Object> countParams = new ArrayList<Object>();
		StringBuilder selectSql = new StringBuilder();
		StringBuilder countQuery = new StringBuilder();
		StringBuilder fromQuery = new StringBuilder();
		
		selectSql.append(" SELECT ac.id  	as id, ");
		selectSql.append(" ac.name 			as name, ");
		selectSql.append(" ac.description 	as description, ");
		selectSql.append(" ac.status 		as status, ");
		selectSql.append(" ac.parent_id 	as parentId, ");
		selectSql.append(" acp.name 		as parentName ");
		
		fromQuery.append(" FROM article_category ac ");
		fromQuery.append(" 		LEFT JOIN article_category acp ");
		fromQuery.append(" 			ON ac.parent_id = acp.id ");
		
		fromQuery.append(" WHERE ac.status != ? ");
		paramSelect.add(ArticleCategoryStatus.DELETED.getValue());
		countParams.add(ArticleCategoryStatus.DELETED.getValue());
		
		if (filter.getId() != null) {
			fromQuery.append(" and ac.id = ? ");
			paramSelect.add(filter.getId());
			countParams.add(filter.getId());
		}
		
		if (!StringUtil.isNullOrEmpty(filter.getNameText())) {
			fromQuery.append(" and (upper(ac.name_text) like upper(?) ESCAPE '/' )");
			paramSelect.add("%" + StringUtil.toSearchText(filter.getNameText())+ "%");
			countParams.add("%" + StringUtil.toSearchText(filter.getNameText())+ "%");
		}
		
		if (!StringUtil.isNullOrEmpty(filter.getDescription())) {
			fromQuery.append(" and (upper(ac.description) like upper(?) ESCAPE '/' )");
			paramSelect.add("%" + StringUtil.toSearchText(filter.getDescription())+ "%");
			countParams.add("%" + StringUtil.toSearchText(filter.getDescription())+ "%");
		}
		
		if (filter.getStatus() != null) {
			fromQuery.append(" and ac.status = ? ");
			paramSelect.add(filter.getStatus());
			countParams.add(filter.getStatus());
		}
		
		if (filter.getParentId() != null) {
			fromQuery.append(" and ac.parent_id = ? ");
			paramSelect.add(filter.getParentId());
			countParams.add(filter.getParentId());
		}
		
		selectSql.append(fromQuery).append(" ORDER BY ac.created_time DESC  , ac.id DESC ");
		
		countQuery.append("SELECT COUNT(*) AS COUNT ").append(fromQuery);

		String[] fieldNames = new String[] { 
				"id", "name", 
				"description", "status", 
				"parentId","parentName"
				};
		
		Type[] fieldTypes = new Type[] { 
				StandardBasicTypes.INTEGER,StandardBasicTypes.STRING, 
				StandardBasicTypes.STRING, StandardBasicTypes.INTEGER, 
				StandardBasicTypes.INTEGER, StandardBasicTypes.STRING
				};

		List<Class<?>> synchronizedClass = new ArrayList<Class<?>>();
		synchronizedClass.add(ArticleCategory.class);

		List<ReportSubSearchVO> list = null;
		if (filter.getPaging() != null) {
			list = repo.getListByQueryAndScalarPaginated(
					ReportSubSearchVO.class, fieldNames, fieldTypes,
					selectSql.toString(), countQuery.toString(), paramSelect,
					countParams, filter.getPaging(), synchronizedClass);
		} else {
			list = repo.getListByQueryAndScalar(ReportSubSearchVO.class,
					fieldNames, fieldTypes, selectSql.toString(), paramSelect,
					synchronizedClass);
		}

		return list;		
	}

}
